/**
 * 项目名：化工企业安全生产信息化管理平台
 * 日期：    2020-03-17 14:00:29
 * Copyright (c) 2015- -版权所有
 */

package com.joysuch.wwyt.edu.repository;

import com.joysuch.wwyt.edu.bean.EduTrainingPlanListVo;
import com.joysuch.wwyt.edu.entity.EduTrainningPlan;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * EduTrainningPlanDao
 */
public interface EduTrainningPlanDao
        extends JpaRepository<EduTrainningPlan, Long>, QuerydslPredicateExecutor<EduTrainningPlan> {
    public Page<EduTrainningPlan> findAll(Specification<EduTrainningPlan> spec, Pageable pageable);

    public EduTrainningPlan save(EduTrainningPlan bean);

    public void delete(EduTrainningPlan bean);

    @Query("select count(m) from EduTrainningPlan m where code = ?1")
    long countOfCode(String code);

    @Query("select count(m) from EduTrainningPlan m where code = ?1 and id != ?2")
    long countOfCodeExcludeId(String code, Long id);

    @Query("select count(m) from EduTrainningPlan m where name = ?1")
    long countOfName(String name);

    @Query("select count(m) from EduTrainningPlan m where name = ?1 and id != ?2")
    long countOfNameExcludeId(String name, Long id);

    @Query("select name from EduTrainningPlan where id = ?1")
    public String getNameById(Long id);

    @Query("select id from EduTrainningPlan where name like %?1%")
    public List<Long> queryIdsByNameContain(String codeOrPlanName);

    @Query(value = "select p.* from wwyt_edu_trainning_plan p where DATE_FORMAT(p.create_time, '%Y')=:year", nativeQuery = true)
    List<EduTrainningPlan> findByYear(String year);

    @Query(value = "select  * from wwyt_edu_trainning_plan   where implement_time is not null and implement_time between ?1 and ?2 and DELETE_FLAG = '0'", nativeQuery = true)
    List<EduTrainningPlan> findByDate(Date startDate, Date endDate);

    @Query(value = "SELECT " +
            "p.id, " +
            "p.CODE as code, " +
            "p.NAME as name," +
            "p.my_train_status as myTrainStatus,p.state," +
            "(" +
            "SELECT " +
            "COUNT( 1 ) " +
            "FROM " +
            "wwyt_edu_trainning_plan_course AS pc " +
            "INNER JOIN wwyt_edu_course AS c ON pc.COURSE_ID = c.id " +
            "WHERE " +
            "pc.TRAINNING_PLAN_ID = p.id " +
            "AND pc.DELETE_FLAG = '0' " +
            "AND c.DELETE_FLAG = '0' " +
            ") AS courseNum," +
            "(" +
            "SELECT " +
            "COUNT( 1 ) " +
            "FROM " +
            "wwyt_edu_trainning_record AS r " +
            "INNER JOIN wwyt_edu_trainning_record_user AS ru ON ru.TRAINNING_RECORD_ID = r.id " +
            "WHERE " +
            "r.TRAINNING_PLAN_ID = p.id " +
            "AND ru.USER_ID = ?1 " +
            "AND r.DELETE_FLAG = '0' " +
            "AND ru.DELETE_FLAG = '0' " +
            ") AS trainingRecordNum " +
            "FROM " +
            "wwyt_edu_trainning_plan AS p " +
            "WHERE " +
            "p.DELETE_FLAG = '0' " +
            "AND NOW() > p.IMPLEMENT_TIME " +
            "AND p.FINISH_TIME > NOW() " +
            "HAVING " +
            "( courseNum > 0 OR trainingRecordNum > 0 )", nativeQuery = true)
    List<Map<String,Object>> findUserPlans(Long userId);

    @Query(value = "SELECT " +
            "p.id, " +
            "p.CODE as code, " +
            "p.NAME as name," +
            "p.my_train_status as myTrainStatus," +
            "(" +
            "SELECT " +
            "COUNT( 1 ) " +
            "FROM " +
            "wwyt_edu_trainning_plan_course AS pc " +
            "INNER JOIN wwyt_edu_course AS c ON pc.COURSE_ID = c.id " +
            "WHERE " +
            "pc.TRAINNING_PLAN_ID = p.id " +
            "AND pc.DELETE_FLAG = '0' " +
            "AND c.DELETE_FLAG = '0' " +
            ") AS courseNum," +
            "(" +
            "SELECT " +
            "COUNT( 1 ) " +
            "FROM " +
            "wwyt_edu_trainning_record AS r " +
            "INNER JOIN wwyt_edu_trainning_record_user AS ru ON ru.TRAINNING_RECORD_ID = r.id " +
            "WHERE " +
            "r.TRAINNING_PLAN_ID = p.id " +
            "AND ru.USER_ID = :userId " +
            "AND r.DELETE_FLAG = '0' " +
            "AND ru.DELETE_FLAG = '0' " +
            ") AS trainingRecordNum " +
            "FROM " +
            "wwyt_edu_trainning_plan AS p " +
            "WHERE " +
            "p.DELETE_FLAG = '0' " +
            "and if(:status is null,1=1,p.my_train_status = :status) " +
            "HAVING " +
            "( courseNum > 0 OR trainingRecordNum > 0 )", nativeQuery = true)
    Page<List<Map<String,Object>>>  findAllUserPlans(@Param("userId")Long userId, @Param("status") String status, Pageable pageable);


    @Query("select m.id from EduTrainningPlan m where m.code = ?1")
    Long findByCode(String code);
}
